package org.eclipse.epsilon.emc.rdbms.core.persistancemechanism;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.eclipse.epsilon.emc.rdbms.core.customexceptions.CustomException;
import org.eclipse.epsilon.emc.rdbms.core.imodel.DBReflexiveModelElement;

public class SQLDatabase extends RelationalDatabase
{
		String    selectMethod;
		
		public SQLDatabase(String URLConnection, String serverName, String portNumber,String databaseName, String userName,String password, String selectMethod)
		       {
			  	 this.URLConnection=URLConnection;
			  	 this.serverName=serverName;
			  	 this.databaseName=databaseName;
			  	 this.portNumber=portNumber;
			  	 this.userName = userName;
			  	 this.password = password;
			  	 this.selectMethod=selectMethod;
			   }	
		
		    /** This method gets the connection to the database.
		    * @return  connection  - a connection to the database
		   */	  
		   public Connection getConnection()
		   {
		  	//This is the driver for the SQL Server 2012.
		        try{
		      	  Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		             connection = java.sql.DriverManager.getConnection(URLConnection+serverName+":"+portNumber+";databaseName="+databaseName+";selectMethod="+selectMethod,userName,password);     
		           }
		         catch(Exception e)
		            {
		             e.printStackTrace();
		             new CustomException("Error Trace in getConnection() : " + e.getMessage());
		           }   	 
		        return connection;
		    }

		/** This method returns the Specific SQL statement to begin a transaction
		* @return  String to begin a transaction.
		*/	  
		@Override
		public String getBeginTransaction()
		{
		
			return "BEGIN TRAN";
		}

		/** This method returns the Specific SQL statement to commit a transaction
		* @return  String to commit a transaction.
		*/	  
		@Override
		public String getCommitTransaction() 
		{
			
			return "COMMIT TRAN";
		}

		/** This method returns the Specific SQL statement to rollback a transaction
		* @return  String to begin a transaction.
		*/	  
		@Override
		public String getRollbackTransaction() 
		{
		return "ROLLBACK TRAN";
		}
		
		  public ResultSet getSQLTables(String tablename) 
		  {
			try
		       {
		    	connection= this.getConnection();  
				if(connection!=null)               
		            {  
					String[] type = new String[1];
	            	type[0] = "TABLE";
		            	ResultSet rs = connection.getMetaData().getTables(databaseName.toUpperCase(), "dbo", tablename, type); //Return a list of all the database tables.
		                return rs;    
		             } 
		            else throw new CustomException("Error: Database Connection set incorrectly"); 
		        }
		        catch(Exception e)
		        {
		             e.printStackTrace();
		        }
				return null;
		  } 
		
		  public ResultSet getPrimaryKey(String tablename)
			{
			       try
			       {
			            connection= this.getConnection();
			            if(connection!=null)
			            { 
			            	ResultSet rs = connection.getMetaData().getPrimaryKeys(null, "dbo", tablename);
			            	
			            	return rs;
			          	    
			            }
			            else throw new CustomException("Error: No Connection defined");
			        }
			        catch(Exception e)
			        {
			             e.printStackTrace();
			        }
			        return null;
			  }
		  
		  
		  public ResultSet getSQLColumns(String tablename) 
		   {
		  	try
		  	{
		  		 connection= this.getConnection();
		           if(connection!=null)
		           {	      	        	   
		        	      DatabaseMetaData dbmd = connection.getMetaData();
		        	      dbmd.supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE);
		        	      
		        	      ResultSet rs = dbmd.getColumns(databaseName.toUpperCase(), "dbo",tablename,"%");
		        	return rs;      
		           } 
		           else throw new CustomException("WARNING: No Database Connection was set");
		      }
		  	catch(Exception e)
		      {
		           e.printStackTrace();
			 }
		      return null;
		   }

		  public ResultSet getExportedKeys(DBReflexiveModelElement foreignElement) throws SQLException
		  {
			  ResultSet primaryElementTableInfo = getSQLTables(foreignElement.getType());
			  String foreigndatabaseName=null;
			  String foreignschemaName=null;
			  String foreigntableName=null;
			  
	          while(primaryElementTableInfo.next())
	          {
	        	  foreigndatabaseName = primaryElementTableInfo.getString("TABLE_CAT");
	        	  foreignschemaName = primaryElementTableInfo.getString("TABLE_SCHEM");
	        	  foreigntableName = primaryElementTableInfo.getString("TABLE_NAME");
	          }
	        return   connection.getMetaData().getExportedKeys(foreigndatabaseName, foreignschemaName, foreigntableName);
		  }
		  
		  public ResultSet getImportedKeys(String element) throws SQLException
		  {
			  ResultSet primaryElementTableInfo = getSQLTables(element);
			  String primarydatabaseName=null;
			  String primaryschemaName=null;
			  String primarytableName=null;
			  
	          while(primaryElementTableInfo.next())
	          {
	  			primarydatabaseName = primaryElementTableInfo.getString("TABLE_CAT");
	  			primaryschemaName = primaryElementTableInfo.getString("TABLE_SCHEM");
	  			primarytableName = primaryElementTableInfo.getString("TABLE_NAME");
	          }
	        return   connection.getMetaData().getImportedKeys(primarydatabaseName, primaryschemaName, primarytableName);
		  }


		  
		
	}
